/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package baiwa.rta.dao.Impl;

import static java.text.MessageFormat.format;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import baiwa.rta.dao.WarningLetterDao;
import baiwa.rta.dao.mapping.WarningLetterMapping;
import baiwa.rta.model.DataTableModel;
import baiwa.rta.model.RMwnltModel;
import baiwa.rta.model.TowerModel;
import baiwa.rta.utils.CommonUtils;

import java.text.MessageFormat;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class WarningLetterDaoImpl implements WarningLetterDao {
    
    private static final Logger logger = Logger.getLogger(WarningLetterDaoImpl.class);

    @Autowired
    JdbcTemplate jdbcTemplate;

    private static final String SQL_QUERY_RMWNLT_ALL = "SELECT * FROM rmwnlt ";
    private static final String SQL_QUERY_RMWNLT_BY_ID = "SELECT wn.*, rm.* FROM rmwnlt wn INNER JOIN rmrmlst rm ON wn.rlrmid = rm.rlrmid where wnltid = ?";

    @Override
    public List<RMwnltModel> findAll() {

        List<RMwnltModel> list = jdbcTemplate.query(SQL_QUERY_RMWNLT_ALL, new Object[]{}, new WarningLetterMapping());

        return list == null ? new ArrayList<RMwnltModel>() : list;
    }

    @Override
    public RMwnltModel findById(Integer wnltid) {

        List<RMwnltModel> model = jdbcTemplate.query(SQL_QUERY_RMWNLT_BY_ID, new Object[]{wnltid}, new WarningLetterMapping());

        return model.size() <= 0 ? new RMwnltModel() : model.get(0);
    }

    private static final String SQL_INSERT_RMWNLT = "INSERT INTO rmwnlt(wnltdocno,wnltstat,wnltattn,wnltdat,wnltsbj,wnltdtl,wnltfilepath,wnltcreby,wnltcredat,wnltupdby,wnltupddat,rlrmid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";

    @Override
    public void create(final RMwnltModel model) throws Exception {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_RMWNLT), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setString(++i, model.getWnltdocno() == null ? null : model.getWnltdocno());
                ps.setString(++i, model.getWnltstat() == null ? null : model.getWnltstat());
                ps.setString(++i, model.getWnltattn() == null ? null : model.getWnltattn());
                ps.setDate(++i, model.getWnltdat() == null ? null : new java.sql.Date(model.getWnltdat().getTime()));
                ps.setString(++i, model.getWnltsbj() == null ? null : model.getWnltsbj());
                ps.setString(++i, model.getWnltdtl() == null ? null : model.getWnltdtl());
                ps.setString(++i, model.getWnltfilepath() == null ? null : model.getWnltfilepath());
                ps.setString(++i, model.getWnltcreby() == null ? null : model.getWnltcreby());
                ps.setDate(++i, model.getWnltcredat() == null ? null : new java.sql.Date(model.getWnltcredat().getTime()));
                ps.setString(++i, model.getWnltupdby() == null ? null : model.getWnltupdby());
                ps.setDate(++i, model.getWnltupddat() == null ? null : new java.sql.Date(model.getWnltupddat().getTime()));
                ps.setInt(++i, model.getRlrmid() == 0 ? 0 : model.getRlrmid());
                return ps;
            }

        }, keyHolder);

    }
    
    private static final String SQL_UPDATE_RMWNLT = "UPDATE rmwnlt SET wnltdocno=?,wnltstat=?,wnltattn=?,wnltdat=?,wnltsbj=?,wnltdtl=?,wnltfilepath=?,wnltcreby=?,wnltcredat=?,wnltupdby=?,wnltupddat=?,rlrmid=? WHERE wnltid=?";

    @Override
    public boolean update(RMwnltModel model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_RMWNLT),
                new Object[]{
                    model.getWnltdocno(),
                    model.getWnltstat(),
                    model.getWnltattn(),
                    model.getWnltdat(),
                    model.getWnltsbj(),
                    model.getWnltdtl(),
                    model.getWnltfilepath(),
                    model.getWnltcreby(),
                    model.getWnltcredat(),
                    model.getWnltupdby(),
                    model.getWnltupddat(),
                    model.getRlrmid(),
                    model.getWnltid()
                });
        return row > 0;
    }
    
    private static final String SQL_DELETE_RMWNLT = "DELETE FROM rmwnlt WHERE wnltid=?";

    @Override
    public boolean delete(RMwnltModel model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_RMWNLT), new Object[]{model.getWnltid()});
        return row > 0;
    }

    @Override
    public DataTableModel<RMwnltModel> findDataTable(RMwnltModel model, int iDisplayStart, int iDisplayLength) {
        DataTableModel<RMwnltModel> dataTableModel = new DataTableModel<RMwnltModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{iDisplayStart, iDisplayStart});
        
        TowerModel tower = model.getTower();
        
         /* --- Where Condition  ---*/
        String sql_where = ""; 
        String sql_join = " LEFT JOIN RMRMLST TW ON TW.RLRMID = ";
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(tower.getTower())) {
            sql_where = sql_where.concat(" AND TW.RLSTTOWER = '" + tower.getTower() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getFloor())) {
        	sql_where = sql_where.concat(" AND TW.RLSTFLOOR = '" + tower.getFloor() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getUnitNo())) {
        	sql_where = sql_where.concat(" AND TW.RLSTUNITNO = '" + tower.getUnitNo() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getAddress())) {
        	sql_where = sql_where.concat(" AND TW.RLSTADDR = '" + tower.getAddress() + "' ");
        }
        
        /*--- Other Condition ----*/
        if (StringUtils.isNotBlank(model.getWnltdocno())) {
        	sql_where = sql_where.concat(" AND A.WNLTDOCNO LIKE '%" + model.getWnltdocno()+ "%' ");
        }
        if (!CommonUtils.isNullOrEmtry(model.getWnltstat())) {
        	sql_where = sql_where.concat(" AND A.WNLTSTAT LIKE '%" + model.getWnltstat()+ "%' ");
        }
        
        if (!CommonUtils.isNullOrEmtry(model.getWnltsbj())) {
        	sql_where = sql_where.concat(" AND A.WNLTSBJ LIKE '%" + model.getWnltstat()+ "%' ");
        }
        
        if (!CommonUtils.isNullOrEmtry(model.getWnltdat())) {
        	sql_where = sql_where.concat(" AND A.WNLTDAT LIKE '%" + model.getWnltstat()+ "%' ");
        }
        
        
        /*--- Initial Query ---*/
        String sqlQuery = " SELECT A.* , TW.* FROM rmwnlt A " + sql_join + " A.RLRMID "
                + " WHERE 1=1 " + sql_where + sqlLimit;
        String sqlCount = "SELECT COUNT(1) FROM rmwnlt A " + sql_join + " A.RLRMID "
                + " WHERE 1=1" + sql_where;

        // --- #1 Get data for datatable by limit row
        List<RMwnltModel> list = jdbcTemplate.query(sqlQuery, new WarningLetterMapping());

        // --- #2 Get row number
        int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(iDisplayStart);
        dataTableModel.setLength(iDisplayLength);

        logger.debug("SQL : " + sqlQuery);
        return dataTableModel;
    }

}
